package com.example.sql4droidbeta;

import java.io.File;
import java.lang.reflect.Method;
import java.util.Calendar;
import java.util.LinkedList;
import java.util.List;

import android.app.ActionBar;
import android.app.ActionBar.Tab;
import android.app.Activity;
import android.app.AlertDialog;
import android.app.Fragment;
import android.app.FragmentTransaction;
import android.content.DialogInterface;
import android.content.Intent;
import android.content.SharedPreferences;
import android.content.SharedPreferences.Editor;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.os.Environment;
import android.support.v13.app.FragmentPagerAdapter;
import android.support.v4.view.ViewPager;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.ExpandableListView;
import android.widget.TextView;
import android.widget.Toast;


public class MainActivity extends Activity implements ActionBar.TabListener, OnClickListener {

	/**
	 * The {@link android.support.v4.view.PagerAdapter} that will provide
	 * fragments for each of the sections. We use a
	 * {@link FragmentPagerAdapter} derivative, which will keep every
	 * loaded fragment in memory. If this becomes too memory intensive, it
	 * may be best to switch to a
	 * {@link android.support.v13.app.FragmentStatePagerAdapter}.
	 */
	private SectionsPagerAdapter mSectionsPagerAdapter;
	private ExpandableListViewAdapter expandableListViewAdapter = null;
	/**
	 * The {@link ViewPager} that will host the section contents.
	 */
	private ViewPager mViewPager;
	private ExpandableListView expandableListView = null;
	private Button btn_create = null;
	private Button btn_open = null;
	private Button btn_refresh = null;
	private TextView txt_dbPath = null;
	private MenuItem menu_run = null;
	
	/*
	 * List 4 Fragments
	 * (non-Javadoc)
	 * @see android.app.Activity#onCreate(android.os.Bundle)
	 */
	private List<PlaceholderFragment> fragmentList = new LinkedList<PlaceholderFragment>();

	// database data field :
	private List<String> group = new LinkedList<String>();
	private List<LinkedList<String>> child = new LinkedList<LinkedList<String>>();
	/*
	 * SQL Tab counter
	 * @see android.app.Activity#onCreate(android.os.Bundle)
	 */
	int sqlCounter = 1;

	@SuppressWarnings("deprecation")
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		
		btn_create = (Button) findViewById(R.id.activitymainButtonCreate);
		btn_open = (Button) findViewById(R.id.activitymainButtonOpenDatabase);
		btn_refresh = (Button) findViewById(R.id.activitymainButtonRefreshList);
		txt_dbPath = (TextView) findViewById(R.id.textView_dbPath);

		// set up buttons' listener
		btn_create.setOnClickListener(this);
		btn_open.setOnClickListener(this);
		btn_refresh.setOnClickListener(this);

		// Set up the action bar.
		final ActionBar actionBar = getActionBar();
		actionBar.setNavigationMode(ActionBar.NAVIGATION_MODE_TABS);
		// actionBar.setDisplayShowTitleEnabled(true);
		// actionBar.setDisplayOptions(ActionBar.DISPLAY_SHOW_HOME, ActionBar.DISPLAY_SHOW_HOME | ActionBar.DISPLAY_USE_LOGO);
		forceActionBarTabAsDoubleLine();

		// template
		group.add("Table");
		group.add("View");
		group.add("Trigger");
		// Load last state from preference
		if(loadLastDatabase()) {
			loadDatabaseInformation(AppGlobal.db);
		} else {
			for(int i = 0; i < group.size(); i ++) {
				LinkedList<String> list = new LinkedList<String>();
				list.add("(*** UnLoad Database ***)");
				child.add(list);
			}
		}

		expandableListViewAdapter = new ExpandableListViewAdapter(this, group, child);
		expandableListView = (ExpandableListView) findViewById(R.id.activitymainExpandableListView1);
		expandableListView.setAdapter(expandableListViewAdapter);
		// expandableListView.setOnItemClickListener(l)


		// Create the adapter that will return a fragment for each of the three
		// primary sections of the activity.
		// add: test for fragmentList if stable !
		// TODO @ 2016.05.25
		SqlStatementsEditFragment f = new SqlStatementsEditFragment();
		f.setTitle("FirstPage");
		fragmentList.add(f);
		mSectionsPagerAdapter = new SectionsPagerAdapter(this, getFragmentManager(), fragmentList);

		// Set up the ViewPager with the sections adapter.
		mViewPager = (ViewPager) findViewById(R.id.pager);
		mViewPager.setAdapter(mSectionsPagerAdapter); 

		// When swiping between different sections, select the corresponding
		// tab. We can also use ActionBar.Tab#select() to do this if we have
		// a reference to the Tab.
		mViewPager.setOnPageChangeListener(new ViewPager.SimpleOnPageChangeListener() {
			@Override
			public void onPageSelected(int position) {
				actionBar.setSelectedNavigationItem(position);
				if(fragmentList.get(position).getType() == PlaceholderFragment.TYPE_EDIT) {
					menu_run.setVisible(true);
				} else {
					menu_run.setVisible(false);
				}
			}
		});

		// For each of the sections in the app, add a tab to the action bar.
		for (int i = 0; i < mSectionsPagerAdapter.getCount(); i++) {
			// Create a tab with text corresponding to the page title defined by
			// the adapter. Also specify this Activity object, which implements
			// the TabListener interface, as the callback (listener) for when
			// this tab is selected.
			Tab tab = actionBar.newTab();
			tab.setText(mSectionsPagerAdapter.getPageTitle(i));
			tab.setTabListener(this);
			actionBar.addTab(tab);
			//            actionBar.getTabAt(actionBar.getTabCount() - 1).getCustomView().setOnClickListener(new View.OnClickListener() {
			//				
			//				@Override
			//				public void onClick(View v) {
			//					Toast.makeText(MainActivity.this, "click", Toast.LENGTH_SHORT).show();
			//				}
			//			});
		}

	}


	private void forceActionBarTabAsDoubleLine() {
		try {  
			Method setHasEmbeddedTabsMethod = getActionBar().getClass().getDeclaredMethod(  
					"setHasEmbeddedTabs", boolean.class);  
			setHasEmbeddedTabsMethod.setAccessible(true);  
			setHasEmbeddedTabsMethod.invoke(getActionBar(), false);  
		} catch (Exception ignore) {  
			ignore.printStackTrace();
		}  
	}

	private boolean loadLastDatabase() {
		SharedPreferences sp = getSharedPreferences("DBINFO", Activity.MODE_PRIVATE);
		String dbPath = sp.getString("DBPATH", null);
		if(null == dbPath) {
			return false;
		} else {
			if(!(new File(dbPath)).exists()) {
				Toast.makeText(this, "Database file not found !", Toast.LENGTH_SHORT).show();
				return false;
			}
			AppGlobal.db = SQLiteDatabase.openOrCreateDatabase(dbPath, null);
			if(null == AppGlobal.db) {
				Toast.makeText(this, "Database Load failed !", Toast.LENGTH_SHORT).show();
				return false;
			}
		}
		Toast.makeText(this, "Database Information has loaded !", Toast.LENGTH_SHORT).show();
		return true;
	}

	private void saveDatabaseInfo() {
		SharedPreferences sp = getSharedPreferences("DBINFO", Activity.MODE_PRIVATE); 
		Editor editor = sp.edit();
		editor.clear();
		if(null != AppGlobal.db) {
			editor.putString("DBPATH", AppGlobal.db.getPath());
		}
		editor.commit();
		Toast.makeText(this, "Database Information has saved !", Toast.LENGTH_SHORT).show();
		return ;
	}

	private LinkedList<String> getTableNames(SQLiteDatabase db) {
		if(null == db) 
			return null;
		LinkedList<String> list = new LinkedList<String>();
		String sql = "select * from sqlite_master where type='table';";
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		for(int i = 0; i < cursor.getCount(); i ++) {
			list.add(cursor.getString(2));
		}
		cursor.close();
		return list;
	}

	private LinkedList<String> getTriggerNames(SQLiteDatabase db) {
		if(null == db) 
			return null;
		LinkedList<String> list = new LinkedList<String>();
		String sql = "select * from sqlite_master where type='trigger';";
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		for(int i = 0; i < cursor.getCount(); i ++) {
			list.add(cursor.getString(cursor.getColumnIndex("name")));
		}
		cursor.close();
		return list;
	}

	private LinkedList<String> getViewNames(SQLiteDatabase db) {
		if(null == db) 
			return null;
		LinkedList<String> list = new LinkedList<String>();
		String sql = "select * from sqlite_master where type='view';";
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		for(int i = 0; i < cursor.getCount(); i ++) {
			list.add(cursor.getString(cursor.getColumnIndex("name")));
		}
		cursor.close();
		return list;
	}

	private boolean createDatabase(String fileName) {
		if(null == fileName) {
			Calendar c = Calendar.getInstance();
			// fileName = "Database_" + c.get(Calendar.YEAR) + "-" + (c.get(Calendar.MONTH) + 1) + "-" + c.get(Calendar.DAY_OF_MONTH) + " " + c.get(Calendar.HOUR) + ":" + c.get(Calendar.MINUTE) + ":" + c.get(Calendar.SECOND);
			fileName = "Database_" + c.get(Calendar.YEAR) + "-" + (c.get(Calendar.MONTH) + 1) + "-" + c.get(Calendar.DAY_OF_MONTH) + " " + (System.currentTimeMillis() % 100000);
		}
		if(fileName.contains("/")) {
			return false;
		}
		File folder = new File(Environment.getExternalStorageDirectory().getAbsolutePath() + File.separator + "SQL4Droid");
		if(!folder.exists()) {
			folder.mkdir();
		}
		// File dbFile = new File(Environment.getExternalStorageDirectory().getAbsolutePath() + File.separator + "SQL4Droid" + File.separator + fileName + ".db");
		// String dbPath = dbFile.getAbsolutePath();
		AppGlobal.db = SQLiteDatabase.openOrCreateDatabase(Environment.getExternalStorageDirectory().getAbsolutePath() + File.separator + "SQL4Droid" + File.separator + fileName + ".db", null);
		return AppGlobal.db == null ? false : true;
	}

	private void showFileChooser() {
		Intent intent = new Intent(Intent.ACTION_GET_CONTENT); 
		intent.setType("*/*"); 
		intent.addCategory(Intent.CATEGORY_OPENABLE);

		try {
			startActivityForResult( Intent.createChooser(intent, "Select a database to open"), 0x5565);
		} catch (android.content.ActivityNotFoundException ex) {
			Toast.makeText(this, "Please install a File Manager.",  Toast.LENGTH_SHORT).show();
		}
	}

	private boolean loadDatabase(final String dbPath, final boolean withLoadInformation) {
		if(null == dbPath)
			return false;
		if(null != AppGlobal.db) {
			AlertDialog.Builder builder = new AlertDialog.Builder(this);
			builder.setTitle("Warnning !");
			builder.setMessage("Do you want to load a new database without database opened ?");
			builder.setPositiveButton("Yes", new DialogInterface.OnClickListener() {							
				@Override
				public void onClick(DialogInterface dialog, int which) {
					// load new database 	
					SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbPath, null);
					if(null != db) {
						AppGlobal.db = db;
						if(withLoadInformation) {
							loadDatabaseInformation(db);
						}
						Toast.makeText(MainActivity.this, "database loaded : " + AppGlobal.db.toString(), Toast.LENGTH_SHORT).show();
					}
				}
			});
			builder.setNegativeButton("No", new DialogInterface.OnClickListener() {							
				@Override
				public void onClick(DialogInterface dialog, int which) {
					dialog.dismiss();								
				}
			});
			builder.show();
		} else {
			AppGlobal.db = SQLiteDatabase.openOrCreateDatabase(dbPath, null);
			if(null == AppGlobal.db) 
				return false;
			Toast.makeText(this, "database loaded : " + AppGlobal.db.toString(), Toast.LENGTH_SHORT).show();
			if(withLoadInformation) {
				loadDatabaseInformation(AppGlobal.db);
			}
		}
		
		return true;
	}

	private boolean loadDatabaseInformation(SQLiteDatabase db){
		if(null == db) {
			for(int i = 0; i < group.size(); i ++) {
				LinkedList<String> list = new LinkedList<String>();
				list.add("(*** UnLoad Database ***)");
				child.add(list);
			}
		} else {
			txt_dbPath.setText("Database opened : " + new File(db.getPath()).getName());
			LinkedList<String> names = getTableNames(db);
			if(0 == names.size()) {
				LinkedList<String> list = new LinkedList<String>();
				list.add("(*** Empty ***)");
				child.add(list);
			}else{
				child.add(names);
			}
			names = getViewNames(AppGlobal.db);
			if(0 == names.size()) {
				LinkedList<String> list = new LinkedList<String>();
				list.add("(*** Empty ***)");
				child.add(list);
			}else{
				child.add(names);
			}
			names = getTriggerNames(AppGlobal.db);
			if(0 == names.size()) {
				LinkedList<String> list = new LinkedList<String>();
				list.add("(*** Empty ***)");
				child.add(list);
			}else{
				child.add(names);
			}
		}
		if(null != expandableListViewAdapter)
			expandableListViewAdapter.notifyDataSetChanged();
		return true;
	}
	private void closeDatabase() {
		if(null == AppGlobal.db) {
			txt_dbPath.setText("no database has been opened now");
			for(int i = 0; i < group.size(); i ++) {
				child.get(i).clear();
			}
			expandableListViewAdapter.notifyDataSetChanged();
			for(int j = 0; j < group.size(); j ++) 
				expandableListView.collapseGroup(j);
		} else {
			AlertDialog.Builder builder = new AlertDialog.Builder(this);
			builder.setTitle("Warning !");
			builder.setMessage("Do you want to close this database ?");
			builder.setPositiveButton("Ok", new DialogInterface.OnClickListener() {	
				@Override
				public void onClick(DialogInterface dialog, int which) {
					txt_dbPath.setText("no database has been opened now");
					for(int i = 0; i < group.size(); i ++) {
						child.get(i).clear();
					}
					expandableListViewAdapter.notifyDataSetChanged();
					for(int j = 0; j < group.size(); j ++) 
						expandableListView.collapseGroup(j);
				}
			});
			builder.setNegativeButton("No", new DialogInterface.OnClickListener() {				
				@Override
				public void onClick(DialogInterface dialog, int which) {
					dialog.dismiss();		
				}
			});
			builder.show();
		}
		return ;
	}

	// Debug Method
	private void printFragmentsInfo() {
		System.out.println("=====================  Start Trace !  ==================");
		for(int i = 0; i < fragmentList.size(); i ++) {
			PlaceholderFragment pf = fragmentList.get(i);
			String type = pf.getType() == PlaceholderFragment.TYPE_EDIT ? "Editor" : "Explorer";
			String title = ((SqlStatementsEditFragment)pf).getTitle();
			String sql = ((SqlStatementsEditFragment)pf).getSQL();
			System.out.println("Fragment: id=" + i + " type=" + type + " title=" + title + " SQL=" + sql);
		}
		return ;
	}

	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// Inflate the menu; this adds items to the action bar if it is present.
		getMenuInflater().inflate(R.menu.main, menu);
		menu_run = menu.findItem(R.id.menu_run_sql);
		return true;
	}

	@Override
	public boolean onOptionsItemSelected(MenuItem item) {
		// Handle action bar item clicks here. The action bar will
		// automatically handle clicks on the Home/Up button, so long
		// as you specify a parent activity in AndroidManifest.xml.
		int id = item.getItemId();
		switch (id) {
		case R.id.menu_add_a_new_page:
			/*FragmentTransaction ft = getFragmentManager().beginTransaction();
			// ft.
			ft.add(R.id.pager, new SqlStatementsEditFragment());
			ft.commit();
			mSectionsPagerAdapter.notifyDataSetChanged();*/
			SqlStatementsEditFragment f = new SqlStatementsEditFragment();
			f.setTitle("MorePage " + sqlCounter++);
			fragmentList.add(f);
			mSectionsPagerAdapter.notifyDataSetChanged();
			Tab tab = getActionBar().newTab(); 
			tab.setText(mSectionsPagerAdapter.getPageTitle(fragmentList.size() - 1));
			tab.setTabListener(this);
			getActionBar().addTab(tab);
			// NOTE : add a new page -> a, add fragment; b, add new actionbar tab item  @ 2016.05.23
			break;
		case R.id.menu_remove_this_page:
			if(getActionBar().getTabCount() > 0) {
				int currentTab = getActionBar().getSelectedNavigationIndex();
				Fragment fragmentToBeRemove = fragmentList.remove(currentTab);
				fragmentToBeRemove.onDestroyView();
				fragmentToBeRemove.onDetach();
				//			FragmentTransaction ft = getFragmentManager().beginTransaction();
				//			ft.remove(fragmentToBeRemove);
				//			ft.commit();
				//			fragmentList.remove(currentTab);
				// mSectionsPagerAdapter.
				// mViewPager.removeViewAt(currentTab);
				// mSectionsPagerAdapter.setFragments(fragmentList);
				// mViewPager.removeViewAt(currentTab);
				// mViewPager.invalidate();
				mSectionsPagerAdapter.notifyDataSetChanged();
				getActionBar().removeTabAt(currentTab);
			}
			break;
		case R.id.action_settings:
			// printFragmentsInfo();
			SqlResultExploreFragment e = new SqlResultExploreFragment();
			e.setTitle("ExplorerPage " + sqlCounter++);
			fragmentList.add(e);
			mSectionsPagerAdapter.notifyDataSetChanged();
			Tab tab2 = getActionBar().newTab(); 
			tab2.setTabListener(this);
			tab2.setText(mSectionsPagerAdapter.getPageTitle(fragmentList.size() - 1));
			// tab.setTabListener(this);
			getActionBar().addTab(tab2);
			break;
		default:
			break;
		}
		return super.onOptionsItemSelected(item);
	}

	@Override
	public void onTabSelected(ActionBar.Tab tab, FragmentTransaction fragmentTransaction) {
		// When the given tab is selected, switch to the corresponding page in
		// the ViewPager.
		mViewPager.setCurrentItem(tab.getPosition());
	}

	@Override
	public void onTabUnselected(ActionBar.Tab tab, FragmentTransaction fragmentTransaction) {
	}

	@Override
	public void onTabReselected(ActionBar.Tab tab, FragmentTransaction fragmentTransaction) {
		/*if(tab.getPosition() == getActionBar().getSelectedTab().getPosition()) {
    		Toast.makeText(this, "" + tab.getPosition(), Toast.LENGTH_SHORT).show();
    	}*/
	}


	@Override
	public void onClick(View v) {
		switch (v.getId()) {
		case R.id.activitymainButtonCreate:
			if(null == AppGlobal.db) {

			}
			if(createDatabase(null)) {
				Toast.makeText(this, "Create database done !", Toast.LENGTH_SHORT).show();
			} else {
				Toast.makeText(this, "Create Database failed !", Toast.LENGTH_SHORT).show();
			}
			break;
		case R.id.activitymainButtonOpenDatabase:
			showFileChooser();
			break;

		default:
			break;
		}
	}

	@Override
	protected void onActivityResult(int requestCode, int resultCode, Intent data) {
		switch (requestCode) {
		case 0x5565:
			if(RESULT_OK == resultCode){
				String path = FileUtils.getPath(this, data.getData());
				Log.i("path", path);
				if(null == path)
					return ;
				if(path.endsWith(".db")){
					loadDatabase(path, true);
				}
			}
			break;

		default:
			break;
		}
		super.onActivityResult(requestCode, resultCode, data);
	}

	@Override
	protected void onStop() {
		saveDatabaseInfo();
		super.onStop();
	}

	@Override
	protected void onDestroy() {
		saveDatabaseInfo();
		super.onDestroy();
	}
}
